import mysql.connector


'''
create_connection   连接
close_connection    断开连接
check_tables        检查表



'''

#### 连接到数据库
def create_connection():
    try:
        connection = mysql.connector.connect(
            host='localhost',
            database='learn',
            user='admin',
            password='123456'
        )
        if connection.is_connected():
            print("Connected to MySQL database")
            db_Info = connection.get_server_info()
            print("Connected to MySQL Server version ", db_Info)
            cursor = connection.cursor()
            cursor.execute("select database();")
            record = cursor.fetchone()
            print("You're connected to database: ", record)
            return connection
    except mysql.connector.Error as e:
        print("Error while connecting to MySQL", e)
        return None

#### 断开数据库

def close_connection(connection):
    if connection.is_connected():
        connection.close()
        print("MySQL connection is closed")
    else:
        print("MySQL is not connected!")

def get_tables_name(connection):
    cursor = connection.cursor()
    cursor.execute("SHOW TABLES")
    tables = cursor.fetchall()
    return [table[0] for table in tables]
#### 检查并创建表
def check_tables(connection, table_name):
    try:
        if connection.is_connected():
            cursor = connection.cursor()
            #cursor.execute("""
            #    SELECT COUNT(*)
            #    FROM information_schema.tables
            #    WHERE table_name = 'users'
            #    AND table_schema = DATABASE()
            #""")
            #result=type(str(cursor.fetchone()))
            result = get_tables_name(connection)
            #if cursor.fetchone() == None:   不好使，不知道为啥有没有表fetchone执行结果都恒为None
            if table_name not in result:
                create_table_query = """
                CREATE TABLE users (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    username VARCHAR(255) NOT NULL UNIQUE,
                    password VARCHAR(255) NOT NULL,
                    name VARCHAR(255) NOT NULL
                );
                """
                cursor.execute(create_table_query)
                connection.commit()
                print("Users table not exist, and be created successfully")
            else:
                print("Users table already exists")
    except mysql.connector.Error as error:
        print(f'Failed to check or create table: {error}')

#### 注册，可以作为所有增的接口
def register(connection,username, password, name):
    try:
        if connection.is_connected():
            cursor = connection.cursor()
            cursor.execute(f'SELECT COUNT(*) FROM users WHERE username = %s',(username,))
            result = str(cursor.fetchone())
            if '0' in result:
                cursor.execute("INSERT INTO users (username, password, name) VALUES %s,%s, %s)", (username, password, name,))
                connection.commit()
                print("User registered successfully.")
                return 1
            else:
                print("Username already exists.")
                return 2
            
    except mysql.connector.Error as error:
        print("Failed to register user: {}".format(error))
        return 3
    
    
#### 登录
def login(connection,username,password):
    if username == "admin":
        print(1)
    else:
        try:
            if connection.is_connected():
                cursor = connection.cursor()
                cursor.execute("SELECT password FROM users WHERE username = %s", (username,))
                record = cursor.fetchone()
                if record and password == record[0]:
                    print("Login successful.")
                    return True
                else:
                    print("Invalid username or password.")
                    return False
        except mysql.connector.Error as error:
            print("Failed to login: {}".format(error))
            return False
        
    

#### 显示某人信息
def search_and_display_self(connection, username):
    try:
        if connection.is_connected():
            cursor = connection.cursor()
            cursor.execute("SELECT id, username, name FROM users WHERE username = %s", (username,))
            user = cursor.fetchone()
            if user:
                print(f'User found: ID: {user[0]}, Username: {user[1]}, Name: {user[2]}')
                return user
            else:
                print("User not found.")
                return None
    except mysql.connector.Error as error:
        print("Failed to find user: {}".format(error))
        return None

def update_table(connection, table_name, operation, column_name, column_type=None):
    try:
        if connection.is_connected():
            cursor = connection.cursor()
            if operation == "add":
                if column_type is None:
                    print("Column type must be specified when adding a column.")
                    return
                alter_query = f"ALTER TABLE {table_name} ADD COLUMN {column_name} {column_type}"
                cursor.execute(alter_query)
                print(f"Column '{column_name}' of type '{column_type}' added to table '{table_name}'.")

            elif operation == "drop":
                alter_query = f"ALTER TABLE {table_name} DROP COLUMN {column_name}"
                cursor.execute(alter_query)
                print(f"Column '{column_name}' dropped from table '{table_name}'.")

            else:
                print("Invalid operation. Use 'add' to add a column or 'drop' to delete a column.")
                return

            connection.commit()
            print("Table updated successfully.")

    except mysql.connector.Error as error:
        print(f"Failed to update table: {error}")

def fetch_table_data(connection, table_name):
    try:
        if connection.is_connected():
            cursor = connection.cursor()
            cursor.execute(f"DESCRIBE {table_name}")
            columns = cursor.fetchall()
            column_names = [column[0] for column in columns]
            cursor.execute(f"SELECT * FROM {table_name}")
            rows = cursor.fetchall()
            '''
            ## 测试用
            print(" | ".join(column_names))
            print("-" * (len(column_names) * 10))
            for row in rows:
                print(" | ".join(str(value) for value in row))
            '''
            # 返回列名和表数据
            return column_names, rows

    except mysql.connector.Error as error:
        print(f"Failed to fetch table data: {error}")
        return None, None


def update_user_info(connection, user_id, column_name, new_value):
    try:
        if connection.is_connected():
            cursor = connection.cursor()
            update_query = f"UPDATE users SET {column_name} = %s WHERE id = %s"
            cursor.execute(update_query, (new_value, user_id))
            
            #cursor.execute(f"UPDATE users SET {column_name} = {new_value} WHERE id = {user_id}")
            connection.commit()
            print(f"Value '{column_name}' updated to '{new_value}' for user ID {user_id}.")
    except mysql.connector.Error as error:
        print(f"Failed to update value: {error}")

a=create_connection()                  #建立连接
#print(get_tables_name(a))             #打印所有表名
#check_tables(a, "users")                        #检查表是否存在
#register(a,"test","testpwd","testname")#尝试注册

#login(a,"test","testpwd")              #登录
#search_and_display_self(a,"test")      #查询

#update_table(a, "users", "add", "email", "VARCHAR(255)")# 添加列

#aa, bb = fetch_table_data(a, "users")
#print(aa,bb)
close_connection(a)                    #断开连接